#coding=utf-8
import utils

create_exday_sql = '''
create table if not exists exday
    (id int not null primary key auto_increment,
    code char(6) not null,
    exdate date not null,
    preday_id int,
    nextday_id int,
    unique (code, exdate));
'''
                    
def edlist2db(con, code, dl):
    '''put 按时间顺序排列的exdays list 2 db'''
    
    c = con.cursor()
    c.execute('insert into exday (code, exdate) values (%s, %s)', (code, dl[0]))
    c.execute('select last_insert_id()')    
    last_id = c.fetchone()[0]  
    
    for d in dl[1:]:
        c.execute('insert into exday (code, exdate, preday_id) values (%s, %s, %s)', (code, d, last_id))
        
        c.execute('select last_insert_id()')
        new_insert_id = c.fetchone()[0] 
        
        c.execute( 'update exday set nextday_id = %s where id = %s' , (new_insert_id, last_id))
        last_id = new_insert_id
        
    c.close()

def most_recent_exday_id(con, code):
       
    c = con.cursor()
    c.execute('select id from exday where code=%s and nextday_id is null', code)
    r  = c.fetchone()
    c.close()
    
    if not r:
        return None
        
    day_id  = r[0]
    return day_id

def get_exday_id(con, code, exdate):
    c = con.cursor()
    c.execute('select id from exday where code=%s and exdate=%s', (code, exdate))
    r  = c.fetchone()
    c.close()
    
    if not r:
        return None
    
    day_id = r[0]
    return day_id

def get_exday_by_id(con, exday_id):
    c = con.cursor()
    
    c.execute("select code, exdate,preday_id, nextday_id from exday where id=%s", exday_id)
    
    r  = c.fetchone()
    colname = [col[0] for col in c.description]
    c.close()
    
    if not r:
        return None
    
    return dict(zip(colname, r))
    
def get_exdays_after(con, code, exdate):
    sql = 'select exdate from exday where code=%s and exdate > %s order by exdate'
    c = con.cursor()
    c.execute(sql, (code, exdate))
    
    r = c.fetchall()
    c.close()
    
    if not r:None
    
    days = [d[0].isoformat() for d in r]
    return days
    